using System;
using System.Data.SqlClient;
using System.Data; 

    
        public static class DBSQLHelper
        {
            /// <summary>
            /// Get a Sqlcommnad object for passed in connstr and spname
            /// </summary>
            /// <param name="ConnStr"></param>
            /// <param name="SpName"></param>
            /// <returns></returns>
            public static SqlCommand GetCommand(string ConnStr, string SpName)
            {
                if (string.IsNullOrEmpty(ConnStr) || string.IsNullOrEmpty(SpName))
                    return null;
                SqlConnection conn = new SqlConnection(ConnStr);
                conn.Open();
                SqlCommand dbCommand = new SqlCommand(SpName, conn);
                dbCommand.CommandType = CommandType.StoredProcedure;
                dbCommand.Connection = conn;
                return dbCommand;
            }

            /// <summary>
            /// Get a Sqlcommnad object for passed in connstr and spname
            /// </summary>
            /// <param name="ConnStr"></param>
            /// <param name="SpName"></param>
            /// <returns></returns>
            public static SqlCommand GetCommand(string ConnStr, string SpName,SqlParameter[] sqlParams)
            {
                if (string.IsNullOrEmpty(ConnStr) || string.IsNullOrEmpty(SpName))
                    return null;
                SqlConnection conn = new SqlConnection(ConnStr);
                conn.Open();
                SqlCommand dbCommand = new SqlCommand(SpName, conn);
                dbCommand.CommandType = CommandType.StoredProcedure;
                if (sqlParams != null)
                    dbCommand.Parameters.AddRange(sqlParams);
                dbCommand.Connection = conn;
                return dbCommand;
            }

            /// <summary>
            /// Gets a Dataset for the passed in Connection string and stored proc name
            /// </summary>
            /// <param name="ConnStr"></param>
            /// <param name="SpName"></param>
            /// <returns></returns>
            public static DataSet GetDataSet(string ConnStr, String SpName,SqlParameter[] sqlParams)
            {
                SqlCommand dbCommand = GetCommand(ConnStr, SpName,sqlParams );
                return GetDataSet(dbCommand);
            }
            /// <summary>
            /// Gets a dataset
            /// </summary>
            /// <param name="dbCommand"></param>
            /// <returns></returns>
            public static DataSet GetDataSet(SqlCommand dbCommand)
            {
                DataSet resultSet = new DataSet();
                SqlDataAdapter adap = new SqlDataAdapter(dbCommand);
                adap.Fill(resultSet);
                dbCommand.Connection.Close();
                return resultSet;
            }
            /// <summary>
            /// Gets a Datatable for the passed in Connection string and stored proc name
            /// </summary>
            /// <param name="ConnStr"></param>
            /// <param name="SpName"></param>
            /// <returns></returns>
            public static DataTable GetDataTable(string ConnStr, String SpName,
                SqlParameter[] sqlParams)
            {
                SqlCommand dbCommand = GetCommand(ConnStr, SpName,sqlParams );
                return  GetDataTable(dbCommand);  
            }

            /// <summary>
            /// Returns a Datable from the SqlCommand object passed in 
            /// </summary>
            /// <param name="dbCommand"></param>
            /// <returns></returns>
            public static DataTable GetDataTable(SqlCommand dbCommand)
            {
                DataSet resultSet = GetDataSet(dbCommand );
                if (resultSet == null)
                    return null;
                if (resultSet.Tables == null)
                    return null;
                if (resultSet.Tables.Count > 0)
                    return resultSet.Tables[0];
                return null;
               
            }

            public static DataSet Update(SqlCommand dbCommand,DataTable dtbTable)
            {
                if (dtbTable == null)
                    return null;
                DataSet dstParent = dtbTable.DataSet; 
                using (dbCommand)
                {
                   
                    SqlDataAdapter dataAdapter =
                        new SqlDataAdapter();
                    dataAdapter.SelectCommand  = dbCommand;
                    SqlCommandBuilder commandBuilder =
                        new SqlCommandBuilder(dataAdapter);

                    dataAdapter.Fill(dstParent);
                    
                    // Code to modify data in the DataSet here.

                    //Without the DbCommandBuilder this line would fail.
                    dataAdapter.Update(dstParent);
                }
                return dstParent;

            }


            public static int  ExecuteNonQuery(string ConnStr, String SpName,
                SqlParameter[] sqlParams)
            {
                if (string.IsNullOrEmpty(ConnStr) || string.IsNullOrEmpty(SpName))
                    return 0 ;
                SqlCommand dbCommand = GetCommand(ConnStr, SpName, sqlParams);   
                int intNumRowsAffected = dbCommand.ExecuteNonQuery();
                return intNumRowsAffected;
            }

            /// <summary>
            /// Returns the first row, first column value of the resultset
            /// </summary>
            /// <param name="ConnStr"></param>
            /// <param name="SpName"></param>
            /// <param name="sqlParams"></param>
            /// <returns></returns>
            public static object  ExecuteScalar(string ConnStr, String SpName,
               SqlParameter[] sqlParams)
            {
                if (string.IsNullOrEmpty(ConnStr) || string.IsNullOrEmpty(SpName))
                    return 0;
                SqlCommand dbCommand = GetCommand(ConnStr, SpName, sqlParams);
                return dbCommand.ExecuteScalar();  
            }
        }
    
